if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetWFEMailDecription]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetWFEMailDecription]
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
/************************************************************************************************              
* Name:  [GetWFEMailDecription]                                                                        
* * Purpose: This stored procedure gets the history of completed activities, associated with execution id         
             
*              
*                
* PARAMETERS                
* Name   Description              
* ------------------------- -----------------------------              
* @executionId    execution Id associated with the submission            
*              
************************************************************************************************              
*Call: [GetWFEMailDecription] 6              
************************************************************************************************/             
CREATE PROCEDURE [dbo].[GetWFEMailDecription] --2302         
(              
 @executionId integer               
)              
As Begin            
SET NOCOUNT ON            
            
 Declare @counter int            
 Declare @totalrecords int            
             
 Declare @Email as Table(             
       Id integer Identity(1,1),            
       ExecutionDetailsId integer,            
       EmailDescription nvarchar(max)            
       )            
          
Insert into @Email Select swe.Id as ExecutionDetailsId,            
      swl.CharData as EmailDescription             
       from swactioneventlong swl           
    inner join swActionEventLog swal          
    On Swal.LogMessage = swl.Id           
       inner join swexecutionDetails swe            
       on swe.[Id] = swal.[executionDetailsId]            
       where swe.ExecutionId = @executionId            
       and swe.ActionType in ('Email','Approval')        
       
 if exists (Select 1 from @Email)      
 BEGIN         
   DECLARE @XmlDocumentHandle int            
   DECLARE @XmlDocument nvarchar(max)            
   declare @EmailDesc table ([ExecutionDetailsID] int,EmailDescription nvarchar(max))                      
   Set @counter = 1            
   Set @totalRecords = (Select count(*) from @Email)            
   While @Counter < = @totalRecords            
    BEGIN            
     Declare @ExecutionDetailsId integer            
     Select @ExecutionDetailsId =ExecutionDetailsId,@XmlDocument = EmailDescription from @Email where id = @counter            
  --            
     -- Create an internal representation of the XML document.            
     EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @XmlDocument            
     -- Execute a SELECT statement using OPENXML rowset provider.            
     Insert into @EmailDesc (ExecutionDetailsId, EmailDescription)               
     SELECT @ExecutionDetailsId,rtrim(Ltrim([description])) as EmailDescription                 
     FROM OPENXML (@XmlDocumentHandle, '//message[@type="ActionEvent" or @type="ResourceActivity"]',2)            
     WITH (description  nvarchar(max))            
                 
     Set @counter = @Counter + 1            
              
    END            
    EXEC sp_xml_removedocument @XmlDocumentHandle          
 END         
 Select * from @EmailDesc where 
 (EmailDescription Like '%The Properties for%'  
	or EmailDescription Like '%forwarded%'
	or EmailDescription Like'%approved the action%'
	or EmailDescription Like '%declined the action%'
	or EmailDescription Like '%rejected the action%')       
End 